package by.android.khan.db;

import org.json.JSONException;
import org.json.JSONObject;

import by.android.khan.content.contract.PlaylistContract;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHelper extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "khan.store.db";
	public static final String PLAYLISTS_TABLE = "PLAYLISTS";
	public static final String[] PLAYLISTS_COLUMNS = {PlaylistContract.PlaylistsColumns.ICON_SRC,
													  PlaylistContract.PlaylistsColumns.KIND,
													  PlaylistContract.PlaylistsColumns.HIDE,
													  PlaylistContract.PlaylistsColumns.DESCRIPTION,
													  PlaylistContract.PlaylistsColumns.RELATIVE_URL,
													  PlaylistContract.PlaylistsColumns.IS_DIRTY,
													  PlaylistContract.PlaylistsColumns.IN_TOPIC_BROWSER,
													  PlaylistContract.PlaylistsColumns.TITLE,
													  PlaylistContract.PlaylistsColumns.X_POS,
													  PlaylistContract.PlaylistsColumns.Y_POS,
													  PlaylistContract.PlaylistsColumns.BACKUP_TIMESTAMP,
													  PlaylistContract.PlaylistsColumns.RENDER_TYPE,
													  PlaylistContract.PlaylistsColumns.INIT_CUSTOM_STACK,
													  PlaylistContract.PlaylistsColumns.ASSESSMENT_PROGRESS_KEY,
													  PlaylistContract.PlaylistsColumns.KA_URL,
													  PlaylistContract.PlaylistsColumns.TOPIC_PAGE_URL,
													  PlaylistContract.PlaylistsColumns.STANDALONE_TITLE,
													  PlaylistContract.PlaylistsColumns.EXTENDED_SLUG,
													  PlaylistContract.PlaylistsColumns.ID,
													  PlaylistContract.PlaylistsColumns.TAGS,
													  PlaylistContract.PlaylistsColumns.IN_KNOWLEDGE_MAP};
	public static final int ICON_SRC_COL_INDEX = 0;
	public static final int KIND_COL_INDEX = 1;
	public static final int HIDE_COL_INDEX = 2;
	public static final int DESCRIPTION_COL_INDEX = 3;
	public static final int RELATIVE_URL_COL_INDEX = 4;
	public static final int IS_DIRTY_COL_INDEX = 5;
	public static final int IN_TOPIC_BROWSER_COL_INDEX = 6;
	public static final int TITLE_COL_INDEX = 7;
	public static final int X_POS_COL_INDEX = 8;
	public static final int Y_POS_COL_INDEX = 9;
	public static final int BACKUP_TIMESTAMP_COL_INDEX = 10;
	public static final int RENDER_TYPE_COL_INDEX = 11;
	public static final int INIT_CUSTOM_STACK_COL_INDEX = 12;
	public static final int ASSESSMENT_PROGRESS_KEY_COL_INDEX = 13;
	public static final int KA_URL_COL_INDEX = 14;
	public static final int TOPIC_PAGE_URL_COL_INDEX = 15;
	public static final int STANDALONE_TITLE_COL_INDEX = 16;
	public static final int EXTENDED_SLUG_COL_INDEX = 17;
	public static final int ID_COL_INDEX = 18;
	public static final int TAGS_COL_INDEX = 19;
	public static final int IN_KNOWLEDGE_MAP_COL_INDEX = 20;
	
	public static final String CREATE_PLAYLISTS_TABLE_SQL = "CREATE TABLE "
			+ PLAYLISTS_TABLE + " (" + PLAYLISTS_COLUMNS[ICON_SRC_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[KIND_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[HIDE_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[DESCRIPTION_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[RELATIVE_URL_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[IS_DIRTY_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[IN_TOPIC_BROWSER_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[TITLE_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[X_POS_COL_INDEX] + " INTEGER NOT NULL, "
									 + PLAYLISTS_COLUMNS[Y_POS_COL_INDEX] + " INTEGER NOT NULL, "
									 + PLAYLISTS_COLUMNS[BACKUP_TIMESTAMP_COL_INDEX] + " NUMERIC NOT NULL, "
									 + PLAYLISTS_COLUMNS[RENDER_TYPE_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[INIT_CUSTOM_STACK_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[ASSESSMENT_PROGRESS_KEY_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[KA_URL_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[TOPIC_PAGE_URL_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[STANDALONE_TITLE_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[EXTENDED_SLUG_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[ID_COL_INDEX] + "  VARCHAR PRIMARY KEY, "
									 + PLAYLISTS_COLUMNS[TAGS_COL_INDEX] + " VARCHAR NOT NULL, "
									 + PLAYLISTS_COLUMNS[IN_KNOWLEDGE_MAP_COL_INDEX] + " VARCHAR NOT NULL)";
	
	public static final String DROP_PLAYLISTS_TABLE_SQL = "DROP TABLE IF EXISTS "
			+ PLAYLISTS_TABLE;
	
	public DataBaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			db.beginTransaction();
			db.execSQL(CREATE_PLAYLISTS_TABLE_SQL);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}

	}

	@Override
	public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
		try {
			db.beginTransaction();
			db.execSQL(DROP_PLAYLISTS_TABLE_SQL);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		onCreate(db);

	}
	
	public Cursor getPlaylists() {
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(DataBaseHelper.PLAYLISTS_TABLE, null,
				null, null, null, null,
				null);
		return cursor;
	}
	
	public long addPlaylist(JSONObject jsonObject) {
		SQLiteDatabase db = getWritableDatabase();
		return db.insert(DataBaseHelper.PLAYLISTS_TABLE, null, getContentValues(jsonObject));			
	}
	
	/**
	 * Gets the content values for PersistedCookie POJO.
	 * 
	 * @param pTask
	 *            cookie POJO
	 * @return the content values
	 */
	private ContentValues getContentValues(final JSONObject mPlaylist) {
		ContentValues values = new ContentValues();
		try {
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.ICON_SRC_COL_INDEX], mPlaylist.getString("icon_src"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.KIND_COL_INDEX], mPlaylist.getString("kind"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.HIDE_COL_INDEX], mPlaylist.getBoolean("hide"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.DESCRIPTION_COL_INDEX], mPlaylist.getString("description"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.RELATIVE_URL_COL_INDEX], mPlaylist.getString("relative_url"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.IS_DIRTY_COL_INDEX], mPlaylist.getBoolean("is_dirty"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.IN_TOPIC_BROWSER_COL_INDEX], mPlaylist.getBoolean("in_topic_browser"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.TITLE_COL_INDEX], mPlaylist.getString("title"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.X_POS_COL_INDEX], mPlaylist.getInt("x_pos"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.Y_POS_COL_INDEX], mPlaylist.getInt("y_pos"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.BACKUP_TIMESTAMP_COL_INDEX], mPlaylist.getString("backup_timestamp"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.RENDER_TYPE_COL_INDEX], mPlaylist.getString("render_type"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.INIT_CUSTOM_STACK_COL_INDEX], mPlaylist.getString("init_custom_stack"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.ASSESSMENT_PROGRESS_KEY_COL_INDEX], mPlaylist.getString("assessment_progress_key"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.KA_URL_COL_INDEX], mPlaylist.getString("ka_url"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.TOPIC_PAGE_URL_COL_INDEX], mPlaylist.getString("topic_page_url"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.STANDALONE_TITLE_COL_INDEX], mPlaylist.getString("standalone_title"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.EXTENDED_SLUG_COL_INDEX], mPlaylist.getString("extended_slug"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.ID_COL_INDEX], mPlaylist.getString("id"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.TAGS_COL_INDEX], mPlaylist.getString("tags"));
			values.put(DataBaseHelper.PLAYLISTS_COLUMNS[DataBaseHelper.IN_KNOWLEDGE_MAP_COL_INDEX], mPlaylist.getBoolean("in_knowledge_map"));
		} catch (JSONException e) {
			e.printStackTrace();
		}
		return values;
	}

}
